package com.anda.logistics.dao.impl;

import java.util.List;

import org.hibernate.Query;
import org.hibernate.transform.Transformers;
import org.springframework.stereotype.Component;

import com.anda.logistics.dao.FreightSourceDao;
import com.anda.logistics.map.util.CommUtil;
import com.anda.logistics.model.FreightSource;
import com.anda.logistics.model.FreightSourceHis;
import com.anda.logistics.util.JQGridPager;
import com.anda.logistics.util.JQGridPager.SearchFilter;
import com.anda.logistics.util.JQGridPager.SearchFilterRules;
import com.anda.logistics.util.QueryFilter.SqlFilter;
import com.anda.logistics.util.QueryFilter;
import com.anda.logistics.vo.JsonReturnVo;

@Component
public class FreightSourceDaoImpl extends AndaBaseSuperDao implements
		FreightSourceDao {

	@Override
	public void save(FreightSource freightSource) {
		sessionFactory.getCurrentSession().save(freightSource);
	}
	@Override
	public void save(FreightSourceHis his) {
		sessionFactory.getCurrentSession().save(his);
	}
	@Override
	public List getFreightCountByProvince(){
		return
			this.sessionFactory.getCurrentSession()
				.createSQLQuery(
					new StringBuilder()
						.append("select b.sortnum as \"sortnum\",b.codeValue as \"codeValue\",b.codeText as \"provinceName\" ,count(*) as \"count\" ")
						.append("  from freightsource as a, systemcode  as b ")
						.append(" where a.originProvince = b.codeValue ")
						.append("   and b.codeName = 'regioncode' ")
						.append("   and trim(coalesce(b.parentValue,'')) = '' ")
						.append(" group by b.sortnum,b.codeValue,b.codeText ")
						.append(" order by b.sortnum,b.codeValue" )
						.toString())
				.setResultTransformer(Transformers.TO_LIST)
				.list();
	}
	@Override
	public List<FreightSource> getPagerList(JQGridPager pager, String freightState) {
		StringBuffer queryStr = new StringBuffer();
		queryStr.append("from FreightSource ")
			.append(getPagedWhereClause(pager, freightState))
			.append(" order by id desc ");
		
		Query q = sessionFactory.getCurrentSession()
			.createQuery(queryStr.toString());
		setPagedParameterSql(q,pager, freightState);
		
		q.setFirstResult((pager.getPage() -1) * pager.getRows());
		q.setMaxResults(pager.getRows());
		return q.list();
	}
	
	@Override
	public long getPagedRecords(JQGridPager pager, String freightState) {
		StringBuffer queryString = new StringBuffer();
		queryString.append("select count(*) from FreightSource ");
		queryString.append(getPagedWhereClause(pager, freightState));
		Query q = sessionFactory.getCurrentSession()
				.createQuery(queryString.toString());
		setPagedParameterSql(q,pager, freightState);
		return (Long) ( q.uniqueResult());
	}
	
	private String getPagedWhereClause(JQGridPager pager, String state){
		StringBuffer sb = new StringBuffer();		
		
		SearchFilter filters = pager.getFiltersObj();
		if ( filters != null){
			SearchFilterRules[] rules = filters.getRules();
			for(int i=0; i< rules.length; i++){
				if (i==0){
					sb.append(" where ");
				} else {
					sb.append(filters.getGroupOp());
				}
				if (rules[i].getField().endsWith("Time")){
					sb.append(" DATE(").append(rules[i].getField()).append(")").append(rules[i].getMapedOp()) .append(" ? ");
				} else {
					sb.append(" ").append(rules[i].getField()).append(rules[i].getMapedOp()) .append(" ? ");
				}
			}
		}
		if (state != null) {
			if (sb.length() > 0) {
				sb.append(" and freightState = ? ");
			} else {
				sb.append(" where freightState = ? ");
			}
		}
		return sb.toString();
	}
	
	private void setPagedParameterSql(Query q,JQGridPager pager, String state){
		SearchFilter filters = pager.getFiltersObj();
		int i=0;
		if ( filters != null){
			SearchFilterRules[] rules = filters.getRules();
			for(; i< rules.length; i++){
				q.setString(i, rules[i].getMapedData(true) );				
			}
		}
		if (state != null) {
			q.setInteger(i, Integer.parseInt(state));
		}
	}
	@Override
	public void deleteOne(long id) {
		FreightSource one = new FreightSource(id);
		sessionFactory.getCurrentSession().delete(one);		
	}
	@Override
	public void delete(FreightSource entity) {
		sessionFactory.getCurrentSession().delete(entity);		
	}
	@Override
	public void update(FreightSource freightSource) {
		sessionFactory.getCurrentSession().update(freightSource);
	}
	
	@Override
	public void updateState(FreightSource freightSource , int state) {
		sessionFactory.getCurrentSession()
		.createQuery("update FreightSource set freightState = :freightState where id = :id ")
		.setInteger("freightState", state )
		.setLong("id", freightSource.getId())
		.executeUpdate();
	}
	
	@Override
	public FreightSource getOneById(long freightId) {		
		return (FreightSource) sessionFactory.getCurrentSession().get(FreightSource.class, freightId);
	}
	
	@Override
	public List<FreightSource> getListByQuery(String [] cols, String[] vals, String ids) {
		StringBuffer sql = new StringBuffer();
		sql.append("from FreightSource where freightState = 1 ");
		if (ids != null && ids.length() > 1){
			sql.append(" and id not in (").append(ids.substring(0,ids.length() - 1)).append(") ");
		}
		for(String s: cols){
			sql.append(" and ").append(s).append(" = :").append(s);
		}
		sql.append(" order by id desc ");
		Query query = sessionFactory.getCurrentSession()
			.createQuery(sql.toString());
		for(int i=0; i< cols.length; i++){
			query.setString(cols[i], vals[i]);
		}
		query.setMaxResults(10);
		return query.list();
	}
	@Override
	public JsonReturnVo getListByQuery(QueryFilter qf) {
		JsonReturnVo vo = new JsonReturnVo();
		vo.setStatus("OK");
		StringBuffer sql = new StringBuffer();
		StringBuffer where = new StringBuffer();
		if (qf != null ) {
			if ( qf.getIds() != null && qf.getIds().length > 0){
				where.append( " a.id not in (:ids) ");
			}
			if (qf.getList() != null){
				for (SqlFilter sf: qf.getList()){
					if (where.length() > 0){
						where.append(" and ");
					}
					where.append(sf.isPre()?" a.":" ").append(sf.getSql());
				}
			}
		}
		sql.append("from FreightSource a")
	        .append(where.length()>0?"   where ":"" ).append(where)
	        .append(" order by a.id desc");
		Query query = sessionFactory.getCurrentSession()
				.createQuery(sql.toString()).setMaxResults(10);
		if (qf != null){
			if (qf.getIds() != null && qf.getIds().length > 0){
				query.setParameterList("ids", CommUtil.getLongs(qf.getIds()));
			}
			qf.setSqlFilterValues(query);
		}
		vo.setData(query.list());
		
		System.out.println(sql);
		sql.setLength(0);
		sql.append("select count(*) from FreightSource a ").append(where.length()>0?" where ":"").append(where);
		System.out.println(sql);
		query = sessionFactory.getCurrentSession()
				.createQuery(sql.toString());
		if (qf != null){
			if (qf.getIds() != null && qf.getIds().length > 0){
				query.setParameterList("ids", CommUtil.getLongs(qf.getIds()));
			}
			qf.setSqlFilterValues(query);
		}
		Number n = (Number) query.uniqueResult();
		vo.setCount(n.intValue());
		return vo;
	}

}
